Analyzing Hospital Bed Counts and Confirmed COVID-19 Cases in the United States

The purpose of this notebook is to review hospital bed counts - with particular focus on Childrens Hospitals - and confirmed COVID-19 cases in the United States.

Data Sources:

This notebook combines three sets of data sources extracted via REST APIs; every time you run the the notebook you have the most up to date information from these sources.

Libraries

Ensure you have imported the following libraries.

If you are using conda and/or environments like Databricks, you will only need to install altair and vega_datasets libraries as the others are already included.

In [1]:
# Standard Libraries
import io

# External Libraries
import requests
import numpy as np
import pandas as pd
import altair as alt
from vega_datasets import data

Configurations

Below are the REST API URLs for the Confirmed US cases of COVID-19 (us_confirmed), US Hospital Bed Information (us_hospitals), and Topographical information.

In [2]:
# cases
us_confirmed = 'https://static.usafacts.org/public/data/covid-19/covid_confirmed_usafacts.csv?_ga=2.70208592.1380466858.1585025088-337055049.1585025088'

# hospital beds
us_hospitals = 'https://opendata.arcgis.com/datasets/1044bb19da8d4dbfb6a96eb1b4ebf629_0.csv'

# topographical
topo_usa = 'https://vega.github.io/vega-datasets/data/us-10m.json'
topo_wa = 'https://raw.githubusercontent.com/deldersveld/topojson/master/countries/us-states/WA-53-washington-counties.json'
topo_king = 'https://raw.githubusercontent.com/johan/world.geo.json/master/countries/USA/WA/King.geo.json'

Confirmed COVID-19 Cases by King County (WA State)

Reviewing COVID-19 cases by King County

In [3]:
result = requests.get(us_confirmed)
buffer = io.StringIO(result.content.decode('utf-8'))

df_usa = (
  pd.read_csv(buffer)
    .melt(id_vars=['countyFIPS', 'County Name', 'State', 'stateFIPS'])
    .dropna()
    .rename(columns={
      'countyFIPS': 'county_fips',
      'County Name': 'county',
      'State': 'state',
      'stateFIPS': 'state_fips',
      'variable': 'date',
      'value': 'confirmed',
    }).astype({
      'date': 'datetime64[ns]',
    })
)


display(df_usa.head())
county_fips county state state_fips date confirmed
0 0 Statewide Unallocated AL 1 2020-01-22 0
1 1001 Autauga County AL 1 2020-01-22 0
2 1003 Baldwin County AL 1 2020-01-22 0
3 1009 Blount County AL 1 2020-01-22 0
4 1011 Bullock County AL 1 2020-01-22 0
In [4]:
# Disable max_rows to see more data
alt.data_transformers.disable_max_rows()
Out[4]:
DataTransformerRegistry.enable('default')
In [5]:
counties_usa = alt.Chart(
  df_usa[df_usa['state'].isin(['WA'])]
).mark_line(point=True).encode(
  x='date:T',
  y='confirmed:Q',
  color='county:N',
  tooltip=['state', 'county', 'confirmed', 'date']
).properties(
  width=600,
  height=480,
  title='Washington State Cases by County'
)

counties_usa
Out[5]:

Hospital Capacity Data (USA)

Reviewing hospital bed data

In [6]:
df_beds = (
  pd.read_csv(us_hospitals)
    .rename(columns={
        'X': 'longitude',
        'Y': 'latitude',
        'OBJECTID': 'hospital_id',
        'HOSPITAL_TYPE': 'hospital_type',
        'HOSPITAL_NAME': 'hospital_name',
        'HQ_ADDRESS1': 'hq_address',
    })
)


# childrens hospital bed capacities
display(df_beds[df_beds['hospital_type'] == 'Childrens Hospital'].head())
longitude latitude hospital_id hospital_name hospital_type HQ_ADDRESS hq_address HQ_CITY HQ_STATE HQ_ZIP_CODE COUNTY_NAME STATE_NAME STATE_FIPS CNTY_FIPS FIPS NUM_LICENSED_BEDS NUM_STAFFED_BEDS NUM_ICU_BEDS BED_UTILIZATION Potential_Increase_In_Bed_Capac
837 -71.105286 42.337667 1838 Boston Childrens Hospital Childrens Hospital 300 Longwood Ave NaN Boston MA 2115 Suffolk Massachusetts 25.0 25.0 25025.0 404.0 415.0 121.0 0.794303 -11
962 -83.054336 42.352155 1963 Childrens Hospital of Michigan Childrens Hospital 3901 Beaubien St NaN Detroit MI 48201 Wayne Michigan 26.0 163.0 26163.0 228.0 228.0 48.0 0.639954 0
998 -93.261940 44.956681 1999 Childrens Hospitals and Clinics of Minnesota -... Childrens Hospital 2525 Chicago Ave South NaN Minneapolis MN 55404 Hennepin Minnesota 27.0 53.0 27053.0 279.0 414.0 42.0 0.763482 -135
1081 -88.077959 30.697451 82 USA Childrens & Womens Hospital Childrens Hospital 1700 Center St NaN Mobile AL 36604 Mobile Alabama 1.0 97.0 1097.0 152.0 101.0 NaN 0.626510 51
1168 -112.039999 33.479290 169 Phoenix Childrens Hospital Childrens Hospital 1919 E Thomas Rd NaN Phoenix AZ 85016 Maricopa Arizona 4.0 13.0 4013.0 411.0 433.0 NaN 0.526736 -22
In [7]:
# Hospital Categories
[*df_beds['hospital_type'].unique()]
Out[7]:
['Short Term Acute Care Hospital',
 'Critical Access Hospital',
 'VA Hospital',
 'Psychiatric Hospital',
 'Rehabilitation Hospital',
 'Childrens Hospital',
 'Long Term Acute Care Hospital',
 'Religious Non-Medical Health Care Institution',
 'Department of Defense Hospital']

Geographic Mapping of Hospitals in Washington State

  • Build base geographic mapping of Washington State
  • Build points which geographically maps the hospitals in Washington State
In [8]:
base = alt.Chart(alt.topo_feature(topo_wa, 'cb_2015_washington_county_20m')).mark_geoshape(
    fill='lightgray',
    stroke='blue',
).properties(
    width=1000,
    height=800,
).project(
    type='mercator'
)

points = alt.Chart(df_beds[(df_beds['STATE_NAME'] == 'Washington')]).mark_circle(opacity=.5).encode(
    longitude='longitude:Q',
    latitude='latitude:Q',
    stroke=alt.value('black'),
    fill=alt.value('steelblue'),
    size=alt.Size('NUM_ICU_BEDS:Q', title='ICU Beds'),
    tooltip=[
      alt.Tooltip('STATE_NAME', title='state'),
      alt.Tooltip('hospital_name', title='hospital name'),
      alt.Tooltip('NUM_LICENSED_BEDS', title='licensed beds'),
      alt.Tooltip('NUM_STAFFED_BEDS', title='staffed beds'),
      alt.Tooltip('NUM_ICU_BEDS', title='icu beds'),
      alt.Tooltip('BED_UTILIZATION', title='bed utilization', format='.0%'),
      alt.Tooltip('hospital_type', title='hospital type'),
    ],
).properties(
    title='Washington State Hospitals'
)

(base + points)
Out[8]:

Geographic Mapping of Hospitals in King County

  • Build base geographic mapping of King county
  • Build points which geographically maps the hospitals in King County
In [9]:
base = alt.Chart(alt.topo_feature(topo_king, 'king')).mark_geoshape(
    fill='#ffffee',
    stroke='blue',
).properties(
    width=800,
    height=600,
).project(
    type='mercator'
)

points = alt.Chart(df_beds[(df_beds['COUNTY_NAME'] == 'King')]).mark_point().encode(
    longitude='longitude:Q',
    latitude='latitude:Q',
    size=alt.Size('NUM_ICU_BEDS:Q', title='ICU Beds'),
    color='hospital_type',
    fill='NUM_ICU_BEDS:Q',
    shape=alt.Shape('hospital_type', title='Hospital Type'),
    stroke=alt.value('black'),
    tooltip=[
      alt.Tooltip('STATE_NAME', title='state'), 
      alt.Tooltip('hospital_name', title='hospital name'), 
      alt.Tooltip('NUM_LICENSED_BEDS', title='licensed beds'), 
      alt.Tooltip('NUM_STAFFED_BEDS', title='staffed beds'), 
      alt.Tooltip('NUM_ICU_BEDS', title='icu beds'), 
      alt.Tooltip('BED_UTILIZATION', title='bed utilization', format='.0%'), 
      alt.Tooltip('hospital_type', title='hospital type'),
    ],
).properties(
    title='King County Hospitals'
)

(base + points)
Out[9]:

Geographic Mapping of Childrens Hospitals and Confirmed COVID-19 Cases

  • Build base geographic mapping of the US
  • Build points which geographically maps the childrens hospitals
In [10]:
us_states = alt.topo_feature(topo_usa, 'states')
us_counties = alt.topo_feature(topo_usa, 'counties')
date = df_usa['date'].max()

# state borders
base_states = alt.Chart(us_states).mark_geoshape().encode(
    stroke=alt.value('lightgray'), fill=alt.value('white')
).properties(
    width=1000,
    height=800,
).project(
    type='albersUsa',
)

# confirmed cases by county
base_counties = alt.Chart(us_counties).mark_geoshape().encode(
    color=alt.Color('confirmed:Q', scale=alt.Scale(type='log'), title='Confirmed'),
).transform_lookup(
    lookup='id',
    from_=alt.LookupData(df_usa[(df_usa['date'] == date) & (df_usa['confirmed'] > 0)], 'county_fips', ['confirmed'])
)

points = alt.Chart(df_beds[df_beds['hospital_type'] == 'Childrens Hospital']).mark_point(opacity=0.75).encode(
    longitude='longitude:Q',
    latitude='latitude:Q',
    size=alt.Size('sum(NUM_ICU_BEDS):Q', title='ICU Beds'),
    fill=alt.Fill('sum(bed_utilization):Q', title='Bed Utilization'),
    stroke=alt.value('black'),
    tooltip=[
      alt.Tooltip('STATE_NAME', title='state'), 
      alt.Tooltip('hospital_name', title='hospital name'), 
      alt.Tooltip('NUM_LICENSED_BEDS', title='licensed beds'), 
      alt.Tooltip('NUM_STAFFED_BEDS', title='staffed beds'), 
      alt.Tooltip('NUM_ICU_BEDS', title='icu beds'), 
      alt.Tooltip('BED_UTILIZATION', title='bed utilization', format='.0%'), 
      alt.Tooltip('hospital_type', title='hospital type'),
    ],
).properties(
    title=f'Childrens Hospital Beds and Confirmed COVID-19 Cases by County {date.date()}'
)

(base_states + base_counties + points)
Out[10]:

Geographic Mapping of Hospitals and Confirmed COVID-19 Cases

  • Build base geographic mapping of the US
  • Build points which geographically maps the childrens hospitals
In [11]:
us_states = alt.topo_feature(topo_usa, 'states')
us_counties = alt.topo_feature(topo_usa, 'counties')
date = df_usa['date'].max()

# state borders
base_states = alt.Chart(us_states).mark_geoshape().encode(
    stroke=alt.value('lightgray'), fill=alt.value('white')
).properties(
    width=1000,
    height=800,
).project(
    type='albersUsa',
)

# confirmed cases by county
base_counties = alt.Chart(us_counties).mark_geoshape().encode(
    color=alt.Color('confirmed:Q', scale=alt.Scale(type='log'), title='Confirmed'),
).transform_lookup(
    lookup='id',
    from_=alt.LookupData(df_usa[(df_usa['date'] == date) & (df_usa['confirmed'] > 0)], 'county_fips', ['confirmed'])
)

# remove the filter on `df_beds` to see all hospitals
points = alt.Chart(df_beds).mark_point(opacity=0.75).encode(
    longitude='longitude:Q',
    latitude='latitude:Q',
    size=alt.Size('sum(NUM_ICU_BEDS):Q', title='ICU Beds'),
    fill=alt.Fill('sum(bed_utilization):Q', title='Bed Utilization'),
    stroke=alt.value('black'),
    tooltip=[
      alt.Tooltip('STATE_NAME', title='state'), 
      alt.Tooltip('hospital_name', title='hospital name'), 
      alt.Tooltip('NUM_LICENSED_BEDS', title='licensed beds'), 
      alt.Tooltip('NUM_STAFFED_BEDS', title='staffed beds'), 
      alt.Tooltip('NUM_ICU_BEDS', title='icu beds'), 
      alt.Tooltip('BED_UTILIZATION', title='bed utilization', format='.0%'), 
      alt.Tooltip('hospital_type', title='hospital type'),
    ],
).properties(
    # update figure title
    title=f'Hospital Beds and Confirmed COVID-19 Cases by County {date.date()}'
)

(base_states + base_counties + points)
Out[11]:
In [21]: